package org.bonn.ooka.persistence;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.Properties;

import org.bonn.ooka.model.ComponentPointer;

public class DBConnector implements IDBConnector {
	private Connection conn = null;
	private String userName = "postgres";
	private String password = "yawl";

	public DBConnector() throws SQLException {
		this.getConnection();
	}

	@Override
	public boolean persistConfiguration(
			Map<String, ComponentPointer<String>> pointerList) {
		try {
			if ( !this.conn.isClosed() ) {
				ResultSet set = this.conn.createStatement().executeQuery(
						"select nextval('num')");
				int nextId = -1;
				while ( set.next() ) {
					nextId = set.getInt("nextval");
				}

				for (ComponentPointer<String> val : pointerList.values()) {
					PreparedStatement prep = this.conn
							.prepareStatement("insert into configuration values (?, ?, ?, ?, ?, ?)");
					prep.setInt(1, nextId);
					prep.setString(2, val.index());
					prep.setString(3, val.index());
//					prep.setString(3, val.getPath());
					prep.setString(4, val.name());
					prep.setInt(5, val.id());
					prep.setString(6, val.state().toString());
					prep.execute();
				}
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	@Override
	public Map<String, ComponentPointer<String>> getConfiguration() {
		// TODO Auto-generated method stub
		return null;
	}

	private void getConnection() throws SQLException {
		Properties connectionProps = new Properties();
		connectionProps.put("user", this.userName);
		connectionProps.put("password", this.password);
		this.conn = DriverManager.getConnection(
				"jdbc:postgresql://localhost:5432/ooka", connectionProps);
		if (!this.conn.isClosed()) {
			try{
				this.conn.prepareStatement("create sequence num increment by 1 start with 1").execute();
			}catch(Exception e){
				System.out.println("DBConnector.getConnection(), sequence num already exists");
			}
		}
		System.out.println("Connected to database");
	}

	private boolean clear() {
		try {
			if (!this.conn.isClosed()) {
				this.conn.prepareStatement("delete from configuration")
						.execute();
				return this.conn.prepareStatement("drop sequence num")
						.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	@Override
	public boolean closeConnection() {
		if (this.conn != null) {
			try {
				this.clear();
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return true;
	}

}
